﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Infrastructure.DAL.SeedWork.Extension;

namespace Infrastructure.Repository.EntityFramework.Extension
{
    public class SqlQuery : IQuery
    {
        private readonly SqlConnection _connection;

        public SqlQuery(string connectionString)
        {
            _connection = new SqlConnection(ConfigurationManager.ConnectionStrings[connectionString].ConnectionString);
        }

        public DataTable GetDataTable(string queryString)
        {
            if (_connection == null) throw new NullReferenceException("SqlConnection");

            var cmd = new SqlCommand();
            bool mustCloseConnection;
            prepareCommand(cmd, _connection, null, CommandType.Text, queryString, null, out mustCloseConnection);

            using (var da = new SqlDataAdapter(cmd))
            {
                var dt = new DataTable();

                da.Fill(dt);

                cmd.Parameters.Clear();

                if (mustCloseConnection)
                    _connection.Close();

                return dt;
            }
        }

        /// <summary>
        ///     预处理用户提供的命令,数据库连接/事务/命令类型/参数
        /// </summary>
        /// <param name="command">要处理的SqlCommand</param>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">一个有效的事务或者是null值</param>
        /// <param name="commandType">命令类型 (存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">存储过程名或都T-SQL命令文本</param>
        /// <param name="commandParameters">和命令相关联的IDataParameter参数数组,如果没有参数为'null'</param>
        /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
        private static void prepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction,
            CommandType commandType, string commandText, IEnumerable<IDataParameter> commandParameters,
            out bool mustCloseConnection)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");

            // If the provided connection is not open, we will open it 
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // 给命令分配一个数据库连接. 
            command.Connection = connection;

            // 设置命令文本(存储过程名或SQL语句) 
            command.CommandText = commandText;

            // 分配事务 
            if (transaction != null)
            {
                if (transaction.Connection == null)
                    throw new ArgumentException(
                        "The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
                command.Transaction = transaction;
            }

            // 设置命令类型. 
            command.CommandType = commandType;

            // 分配命令参数 
            if (commandParameters != null)
            {
                attachParameters(command, commandParameters);
            }
        }

        private static void attachParameters(SqlCommand command, IEnumerable<IDataParameter> commandParameters)
        {
            if (command == null) throw new ArgumentNullException("command");
            if (commandParameters != null)
            {
                foreach (IDataParameter p in commandParameters)
                {
                    if (p != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value. 
                        if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }
                }
            }
        }
    }
}